﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using TugasAkhir.Helpers;

namespace TugasAkhir.Models
{
    public static class PerhitunganModel
    {
        private static bool GetJumlah(string idMatakuliah, string tahun, string periode, out int total, string format)
        {
            var hasilQuery = new DataSet();
            string query = String.Format(format, tahun, periode, idMatakuliah);
            bool flag = SqlServer.ExecuteReader(query, out hasilQuery);
            if (flag)
            {
                if (hasilQuery.Tables[0] != null && hasilQuery.Tables[0].Rows.Count > 0)
                {
                    total = int.Parse(hasilQuery.Tables[0].Rows[0][0].ToString());
                    return true;
                }
                else
                {
                    total = -1;
                    return false;
                }
            }
            else
            {
                total = -1;
                return false;
            }
        }

        public static bool GetJumlahPeserta(string idMatakuliah, string tahun, string periode, out int total)
        {
            return GetJumlah(idMatakuliah, tahun, periode, out total, "SELECT COUNT(*) AS jumlah_mahasiswa FROM [dbo].[pengambilan_mahasiswa] WHERE [tahun] = '{0}' AND [periode] = '{1}' AND [id_matakuliah] = '{2}'");
        }

        public static bool GetJumlahPertanyaan(string idMatakuliah, string tahun, string periode, out int total)
        {
            return GetJumlah(idMatakuliah, tahun, periode, out total, "SELECT COUNT(DISTINCT [id_pertanyaan]) as jumlah_pertanyaan FROM [dbo].[detail_pertanyaan] WHERE [tahun] = '{0}' AND [periode] = '{1}' AND [id_matakuliah] = '{2}'");
        }

        public static bool GetJumlahPengisi(string idMatakuliah, string tahun, string periode, out int total)
        {
            return GetJumlah(idMatakuliah, tahun, periode, out total, "SELECT COUNT(*) AS [total] FROM ( SELECT DISTINCT [id_mahasiswa], [status_pengisian] FROM [detail_pertanyaan] WHERE [tahun] = '{0}' AND [periode] = '{1}' AND [id_matakuliah] = '{2}' AND [status_pengisian] = 1) AS [pengisian]");
        }

        public static bool GetTabelPertanyaan(string idMatakuliah, string tahun, string periode, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT [id_pertanyaan] FROM [dbo].[detail_pertanyaan] WHERE [tahun] = '{0}' AND [periode] = '{1}' AND [id_matakuliah] = '{2}' ORDER BY [id_pertanyaan]", tahun, periode, idMatakuliah);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetNilaiAkhir(string idMatakuliah, string tahun, string periode, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT PMAT.[jumlah_pertemuan], PMAH.[nilai_akhir] FROM [dbo].[pelaksanaan_matakuliah] PMAT, [dbo].[pengambilan_mahasiswa] PMAH WHERE PMAH.id_matakuliah = PMAT.[id_matakuliah] AND PMAH.[periode] = PMAT.[periode] AND PMAH.[tahun] = PMAT.[tahun] AND PMAT.[id_matakuliah] = '{0}' AND PMAT.[tahun] = '{1}' AND PMAT.[periode] = '{2}'", idMatakuliah, tahun, periode);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetNilaiAkhir(string idMatakuliah, string tahun, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT PMAT.[jumlah_pertemuan], PMAH.[nilai_akhir] FROM [dbo].[pelaksanaan_matakuliah] PMAT, [dbo].[pengambilan_mahasiswa] PMAH WHERE PMAH.id_matakuliah = PMAT.[id_matakuliah] AND PMAH.[periode] = PMAT.[periode] AND PMAH.[tahun] = PMAT.[tahun] AND PMAT.[id_matakuliah] = '{0}' AND PMAT.[tahun] = '{1}'", idMatakuliah, tahun);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetTabelNilai(string idMatakuliah, string tahun, string periode, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT t1.[id_mahasiswa], t1.[id_matakuliah], t1.[periode], t1.[tahun], STUFF( (SELECT ', ' + t2.[kode_jawaban] FROM [detail_pertanyaan] t2 WHERE t1.[id_mahasiswa] = t2.[id_mahasiswa] AND t1.[id_matakuliah] = t2.[id_matakuliah] AND t1.[periode] = t2.[periode] AND t1.[tahun] = t2.[tahun] FOR XML PATH ('')) , 1, 1, '')  AS jawaban FROM [dbo].[detail_pertanyaan] t1 WHERE t1.[tahun] = '{0}' AND t1.[periode] = '{1}' AND t1.[id_matakuliah] = '{2}'", tahun, periode, idMatakuliah);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetTabelNilai(string idMatakuliah, string tahun, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT t1.[id_mahasiswa], t1.[id_matakuliah], t1.[periode], t1.[tahun], STUFF( (SELECT ', ' + t2.[kode_jawaban] FROM [detail_pertanyaan] t2 WHERE t1.[id_mahasiswa] = t2.[id_mahasiswa] AND t1.[id_matakuliah] = t2.[id_matakuliah] AND t1.[periode] = t2.[periode] AND t1.[tahun] = t2.[tahun] ORDER BY t2.[id_pertanyaan] ASC FOR XML PATH ('')) , 1, 1, '')  AS jawaban FROM [dbo].[detail_pertanyaan] t1 WHERE t1.[tahun] = '{0}' AND t1.[id_matakuliah] = '{1}'", tahun, idMatakuliah);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }
    }
}